﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;
using System.Diagnostics;

using ik.DB;
using ik.Functions;

namespace SQLEditor
{
    public enum ViewType
    {
        Grid = 1,
        Text = 2
    }
    public partial class Editor : UserControl
    {
        clDB clDb = null;
        clFunctions clF = new clFunctions();

        Boolean isStoped = false;

        Boolean tratarTxt = true;
        String Error = String.Empty;

        //Int32 PackageSize = 4096;
        List<DataTable> DataTableList = new List<DataTable>();

        Stopwatch stopWatch = new Stopwatch();
        DataTable _dt = new DataTable();

        [DllImport("user32.dll")] // import lockwindow to remove flashing
        public static extern bool LockWindowUpdate(IntPtr hWndLock);

        /// <summary>
        /// Lista de palavras que estão na cor azul
        /// </summary>
        public Regex keyBlueWords = new Regex("SELECT|FROM| AS|WHERE|ORDER|BY|GROUP|INNER|LEFT|RIGHT|JOIN|DISTINCT| DESC| TOP|EXEC ");
        /// <summary>
        /// Lista de palavras que estão na cor rosa
        /// </summary>
        public Regex keyPinkWords = new Regex("COUNT|ON | SUM");
        /// <summary>
        /// Lista de palavras que estão na cor cinza
        /// </summary>
        public Regex keyGrayWords = new Regex("AND ");

        public Editor()
        {
            InitializeComponent();
        }

        private void Editor_Load(object sender, EventArgs e)
        {

        }

        public void SetConnection(Object ObjectConnection)
        {
            this.clDb = (clDB)ObjectConnection;
        }

        private void Execute(ViewType type)
        {
            CheckForIllegalCrossThreadCalls = false;

            this.txtMessage.Text = String.Empty;
            this.txtMessage.ForeColor = Color.Black;

            
            stopWatch.Start();
            String strSQL = this.txtQuery.Text;

            // CASO NÃO ESTEJA CONECTADO, É NECESSÁRIO CHAMAR A TELA GERENCIADOR DE CONEXÕES.
            if (this.clDb == null)
            {
                //((Form)this.ParentForm).Invoke(new Delegate() t
            }

            if (String.IsNullOrEmpty(strSQL))
            {
                strSQL = "SELECT TOP 1000 * FROM Empresa";
            }

            Thread td = new Thread(new ParameterizedThreadStart(ExecuteInDataBase));
            td.Start(strSQL);

            this.cmdStop.Enabled = true;
            
            while (td.IsAlive)
            {
                if (isStoped)
                {
                    td.Abort();
                }

                TimerChance(stopWatch.Elapsed);
            }

            if (pnlResultGrid.Controls.Count > 0)
            {
                DataGridView _dgv = (DataGridView)pnlResultGrid.Controls[0];
                pnlResultGrid.Controls.Remove(_dgv);
            }

            if (!isStoped)
            {
                DataGridView dgv = new DataGridView();
                dgv.DataSource = _dt;
                dgv.Dock = DockStyle.Fill;
                dgv.CellEnter += new DataGridViewCellEventHandler(dgv_CellEnter);

                this.pnlResultGrid.Controls.Add(dgv);

                this.tpGrid.Show();
            }

            

            GC.Collect();
            
            //if (type == ViewType.Grid)
            //{
            
            //}
            //else
            //{
            //    #region TextView

            //    String txtResult = String.Empty;
            //    String header = String.Empty;
            //    String Separator = "-";

            //    for (int i = 0; i < dt.Columns.Count; i++)
            //    {
            //        Int32 LengthCol = dt.Columns[i].MaxLength;
            //        if (dt.Columns[i].ColumnName.Length > dt.Columns[i].MaxLength)
            //        {
            //            LengthCol = dt.Columns[i].ColumnName.Length + 10;
            //        }
            //        header += dt.Columns[i].ColumnName.PadRight(LengthCol, ' ') + " ";
            //    }
            //    txtResult = header + "\r\n";
            //    txtResult += Separator.PadRight(header.Length, '-') + "\r\n";

            //    for (int i = 0; i < dt.Rows.Count; i++)
            //    {
            //        for (int j = 0; j < dt.Rows[i].ItemArray.Count(); j++)
            //        {
            //            Int32 LengthCol = dt.Columns[j].MaxLength;
            //            if (dt.Columns[j].ColumnName.Length > dt.Columns[j].MaxLength)
            //            {
            //                LengthCol = dt.Columns[j].ColumnName.Length + 10;
            //            }

            //            txtResult += dt.Rows[i].ItemArray[j].ToString().PadRight(LengthCol, ' ') + " ";
            //        }
            //        txtResult += "\r\n";
            //    }

            //    this.txtResultText.Text = txtResult;

            //    #endregion

            //    this.pnlResultGrid.Visible = false;
            //    this.pnlResultText.Visible = true;
            //}

            if (!String.IsNullOrEmpty(Error))
            {
                this.tabControl1.SelectedIndex = 1;
                if (isStoped)
                {
                    this.txtMessage.Text = "Query was cancelled by user.";
                }
                else
                {
                    this.txtMessage.Text = Error;
                    this.txtMessage.ForeColor = Color.Red;
                }

                Error = String.Empty;
            }

            isStoped = false;

            stopWatch.Stop();
            TimerChance(stopWatch.Elapsed);
            stopWatch.Reset();
        }

        void dgv_CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                DataGridView dgv = (DataGridView)this.pnlResultGrid.Controls[0];

            StatusChange(dgv.Rows.Count, e.RowIndex + 1, dgv.Columns.Count, e.ColumnIndex + 1);
            }
            catch
            { }
        }

        private void ExecuteInDataBase(Object Query)
        {
            try
            {
                //this.timerDuration.Enabled = true;
                this.timerDuration_Tick(null, new EventArgs());

                IDataReader _dr = this.clDb.GetDataReader((String)Query);
                _dt = ConvertDataReaderToDataTable(_dr);

                //this.cmdViewGrid.Enabled = false;
                //this.cmdViewText.Enabled = false;

                _dr.Close();
                _dr = null;

                TimerChance(stopWatch.Elapsed);

            }
            catch (Exception ex)
            {
                this.Error = ex.Message;
            }
            finally
            {
                //this.cmdViewGrid.Enabled = true;
                //this.cmdViewText.Enabled = true;
            }
        }

        /// <summary>
        /// [static] ik.Data.Utils.DataReaderConverters.ConvertDataReaderToDataTable
        /// converts SqlDataReader to a DataTable
        /// </summary>
        /// <param name="reader">IDataReader</param>
        /// <returns>System.Data.DataTable</returns>
        public static DataTable ConvertDataReaderToDataTable(IDataReader reader)
        {
            System.Data.DataTable table = reader.GetSchemaTable();
            System.Data.DataTable dt = new System.Data.DataTable();
            System.Data.DataColumn dc;
            System.Data.DataRow row;
            System.Collections.ArrayList al = new System.Collections.ArrayList();

            for (int i = 0; i < table.Rows.Count; i++)
            {
                dc = new System.Data.DataColumn();
                if (!dt.Columns.Contains(table.Rows[i]["ColumnName"].ToString()))
                {
                    dc.ColumnName = table.Rows[i]["ColumnName"].ToString();
                    dc.Unique = Convert.ToBoolean(table.Rows[i]["IsUnique"]);
                    dc.AllowDBNull = Convert.ToBoolean(table.Rows[i]["AllowDBNull"]);
                    dc.ReadOnly = Convert.ToBoolean(table.Rows[i]["IsReadOnly"]);
                    al.Add(dc.ColumnName);
                    dt.Columns.Add(dc);
                }
            }
            while (reader.Read())
            {
                row = dt.NewRow();
                for (int i = 0; i < al.Count; i++)
                {
                    row[((System.String)al[i])] = reader[(System.String)al[i]];
                }
                dt.Rows.Add(row);
            }
            return dt;
        }

        private void StatusChange(Int32 rows, Int32 currentRow, Int32 cols, Int32 currentCol)
        {
            this.sblRow.Text = "Row: " + (currentRow).ToString() + " / " + rows.ToString();
            this.sblColumn.Text = "Col: " + (currentCol).ToString() + "/" + cols.ToString();
        }

        private void TimerChance(TimeSpan ts)
        {
            Application.DoEvents();
            this.sblDuration.Text = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
        }

        /// <summary>
        /// Recebe o conteúdo do arquivo e atribui ao campo de query
        /// </summary>
        /// <param name="File">Conteúdo do arquivo .sql</param>
        public void OpenFile(String File)
        {
            this.txtQuery.Text = File;
        }

        /// <summary>
        /// Retorna a query inserida pelo usuário
        /// </summary>
        /// <returns>Query</returns>
        public String SaveFile()
        {
            return this.txtQuery.Text;
        }

        private void ShowTime()
        {
            TimeSpan ts = stopWatch.Elapsed;
            this.sblDuration.Text = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            
        }

        private void txtQuery_TextChanged(object sender, EventArgs e)
        {
            if (tratarTxt)
            {
                try
                {
                    LockWindowUpdate(this.txtQuery.Handle);

                    int selPos = this.txtQuery.SelectionStart;

                    foreach (Match keyWordMatch in keyBlueWords.Matches(this.txtQuery.Text.ToUpper()))
                    {
                        this.txtQuery.Select(keyWordMatch.Index, keyWordMatch.Length);
                        this.txtQuery.SelectionColor = Color.Blue;

                        this.txtQuery.SelectionStart = selPos;
                        this.txtQuery.SelectionLength = 0;
                        this.txtQuery.SelectionColor = Color.Black;
                    }

                    foreach (Match keyWordMatch in keyPinkWords.Matches(this.txtQuery.Text.ToUpper()))
                    {
                        this.txtQuery.Select(keyWordMatch.Index, keyWordMatch.Length);
                        this.txtQuery.SelectionColor = Color.DeepPink;
                        
                        this.txtQuery.SelectionStart = selPos;
                        this.txtQuery.SelectionLength = 0;
                        this.txtQuery.SelectionColor = Color.Black;
                    }

                    foreach (Match keyWordMatch in keyGrayWords.Matches(this.txtQuery.Text.ToUpper()))
                    {
                        this.txtQuery.Select(keyWordMatch.Index, keyWordMatch.Length);
                        this.txtQuery.SelectionColor = Color.DarkGray;

                        this.txtQuery.SelectionStart = selPos;
                        this.txtQuery.SelectionLength = 0;
                        this.txtQuery.SelectionColor = Color.Black;
                    }
                }
                finally 
                { 
                    LockWindowUpdate(IntPtr.Zero); 
                }
            }

            UpdateLineNumber();
        }

        private void txtQuery_KeyDown(object sender, KeyEventArgs e)
        {
            if ((e.KeyValue >= 65) && (e.KeyValue <= 90))
            {
                tratarTxt = false;
            }
            else
            {
                tratarTxt = true;
            }
        }

        private void txtQuery_KeyPress(object sender, KeyPressEventArgs e)
        {
        }

        private void timerDuration_Tick(object sender, EventArgs e)
        {
            TimeSpan ts = stopWatch.Elapsed;
            this.sblDuration.Text = String.Format("{0:00}:{1:00}:{2:00}.{3:00}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.tabControl1.TabPages.RemoveAt(0);
            this.txtQuery.Focus();
        }

        private void cmdStop_Click(object sender, EventArgs e)
        {
            //

            this.tabControl1.TabPages.RemoveAt(0);
            this.tabControl1.TabPages.Add(this.tpGrid);
            this.tabControl1.TabPages.Add(this.tpMessage);
            
        }

        private void tsbExecute_Click(object sender, EventArgs e)
        {
            this.tsbExecute.Enabled = false;
            this.tsbStop.Enabled = true;

            Execute(ViewType.Grid);
        }

        private void tsbStop_Click(object sender, EventArgs e)
        {
            this.tsbExecute.Enabled = true;
            this.tsbStop.Enabled = false;

            isStoped = true;
        }

        private void UpdateLineNumber()
        {
            Point pos = new Point(0, 0);
            int firstIndex = this.txtQuery.GetCharIndexFromPosition(pos);
            int firstLine = this.txtQuery.GetLineFromCharIndex(firstIndex);

            pos.X = ClientRectangle.Width;
            pos.Y = ClientRectangle.Height;
            int lastIndex = this.txtQuery.GetCharIndexFromPosition(pos);
            int lastLine = this.txtQuery.GetLineFromCharIndex(lastIndex);

            pos = this.txtQuery.GetPositionFromCharIndex(lastIndex);

            numberLabel.Text = "";
            for (int i = firstLine; i <= lastLine; i++)
            {
                numberLabel.Text += i + 1 + "\n";
            }
        }

        private void txtQuery_VScroll(object sender, EventArgs e)
        {
            //int d = this.txtQuery.GetPositionFromCharIndex(0).Y % (this.txtQuery.Font.Height + 1);
            //numberLabel.Location = new Point(0, d);

            UpdateLineNumber();
        }

    }
}